'''
Description: 
Version: 2.0
Autor: zyw
Date: 2022-01-17 15:56:40
LastEditors: zyw
LastEditTime: 2022-03-01 09:18:47
'''
import pymysql
import os, sys
from sshtunnel import SSHTunnelForwarder

base_path = os.path.abspath(os.path.join(os.getcwd(), "."))
sys.path.append(base_path)
from common.hand_conf import conf
from common.hand_path import key_path


class HandMysql(object):

    # 使用初始化方法，建立数据库连接
    def __init__(self, ssh_host, ssh_port, ssh_pkey, ssh_user, ssh_password, host, port, user, password, db):
        # 创建ssh连接服务
        with SSHTunnelForwarder(
                (ssh_host, ssh_port),
                # 服务器连接秘钥
                ssh_pkey=ssh_pkey,
                ssh_password=ssh_password,
                ssh_username=ssh_user,
                remote_bind_address=(host, port)) as self.server:
            pass
        # 启动服务
        self.server.start()
        # 创建数据库连接
        self.conn = pymysql.connect(host=host,
                                    port=self.server.local_bind_port,
                                    user=user,
                                    passwd=password,
                                    db=db,
                                    # 将游标对象设置以字典格式返回查询的数据，默认为元祖
                                    cursorclass=pymysql.cursors.DictCursor,
                                    charset='utf8')

    def query_all(self, sql_str):
        """查询所有数据"""
        cur = self.conn.cursor()
        cur.execute(sql_str)
        res = cur.fetchall()
        cur.close()
        return res

    def query_one(self, sql_str):
        '''查询一条数据'''
        cur = self.conn.cursor()
        cur.execute(sql_str)
        res = cur.fetchone()
        cur.close()
        return res

    def query_count(self, sql_str):
        """查询数据的条数"""
        cur = self.conn.cursor()
        res = cur.execute(sql_str)
        cur.close()
        return res

    def insert_data(self, sql_str):
        cur = self.conn.cursor()
        try:
            cur.execute(sql_str)
            self.conn.commit()

        except Exception as e:
            self.conn.rollback()
            print('插入失败')
            raise e

    def __del__(self):
        """执行完毕后自动销毁对象"""
        self.conn.close()
        self.server.stop()


hand_mysql = HandMysql(
    ssh_host=conf.get('mysql', 'ssh_host'),
    ssh_port=int(conf.get('mysql', 'ssh_port')),
    ssh_pkey=key_path,
    ssh_user=conf.get('mysql', 'ssh_user'),
    ssh_password=conf.get('mysql', 'ssh_password'),
    host=conf.get('mysql', 'host'),
    port=int(conf.get('mysql', 'port')),
    user=conf.get('mysql', 'user'),
    password=conf.get('mysql', 'password'),
    db=conf.get('mysql', 'db_name')
)

if __name__ == '__main__':
    sql = 'select * from t_user where name = "杨平"'
    res = hand_mysql.query_one(sql)
    print(res)
